package io.renren.controller;

import io.renren.entity.*;
import io.renren.service.ExcelService;
import io.renren.service.CrlgService;
import io.renren.service.ExeclFileService;
import io.renren.utils.PageUtils;
import io.renren.utils.R;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecgframework.poi.excel.ExcelImportUtil;
import org.jeecgframework.poi.excel.entity.ExportParams;
import org.jeecgframework.poi.excel.entity.ImportParams;
import org.apache.poi.ss.usermodel.Workbook;
import org.jeecgframework.poi.excel.ExcelExportUtil;
import org.jeecgframework.poi.excel.entity.TemplateExportParams;
import org.jeecgframework.poi.excel.entity.enmus.ExcelType;
import org.jeecgframework.poi.excel.entity.vo.NormalExcelConstants;
import org.jeecgframework.poi.excel.export.ExcelExportServer;
import org.jeecgframework.poi.util.PoiMergeCellUtil;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;


import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;

/**
 * 操作execl的类
 *
 * @author 阿健
 * @create 2017-02-28 22:28
 */
@RequestMapping("/sys/execl")
@Controller
public class ExcelController extends AbstractController{

    @Autowired
    private ExcelService excelService;

    @Autowired
    private CrlgService crlgService;

    @Autowired
    private ExeclFileService execlFileService;

    private static List<String> excelTypeList;

    static {
        excelTypeList = new ArrayList<>();
        excelTypeList.add(".xls");
        excelTypeList.add(".xlsx");
    }

    /**
     * 导出逻辑
     */
//    @RequestMapping(value = "/exportXls.do")
//    public String exportExcelFile(HttpServletRequest request,@RequestParam("fileName")String fileName,ModelMap modelMap) {
//
//                Map<String,Object> map = new HashMap<>();
//                List<ExcelDemoEntity> list = excelService.queryList(map);
//                modelMap.put(NormalExcelConstants.FILE_NAME,"华康清单"+new Date());
//                modelMap.put(NormalExcelConstants.CLASS,ExcelDemoEntity.class);
//                modelMap.put(NormalExcelConstants.PARAMS,new ExportParams("淘宝/天猫清单", "销售时间xxx", "淘宝天猫"));
//                modelMap.put(NormalExcelConstants.DATA_LIST,list);
//                return NormalExcelConstants.JEECG_EXCEL_VIEW;
//
//
//    }

    //导出多sheet
    private Map<String,Object> getTestMap(String num,List data){
        Map<String ,Object> map =new LinkedHashMap<String,Object>();
        if(num.equals("1")){
            map.put(NormalExcelConstants.CLASS,TianMaoDemoEntity.class);
            map.put(NormalExcelConstants.PARAMS,new ExportParams("淘宝/天猫清单", "销售时间xxx", "木糖醇"));
            map.put(NormalExcelConstants.DATA_LIST,data);
        }else if(num.equals("2")){

            map.put(NormalExcelConstants.CLASS,FruitDemoEntity.class);
            map.put(NormalExcelConstants.PARAMS,new ExportParams("淘宝/天猫清单", "销售时间xxx", "果糖"));
            map.put(NormalExcelConstants.DATA_LIST,data);
        }else if(num.equals("4")){
            map.put(NormalExcelConstants.CLASS,TaobaoDemoEntity.class);
            map.put(NormalExcelConstants.PARAMS,new ExportParams("淘宝/天猫清单", "销售时间xxx", "木糖醇"));
            map.put(NormalExcelConstants.DATA_LIST,data);
        }
        //京东新店
        else if(num.equals("5")){
            map.put(NormalExcelConstants.CLASS,JingDongDemoEntity.class);
            map.put(NormalExcelConstants.PARAMS,new ExportParams("京东新店清单", "销售时间xxx", "木糖醇"));
            map.put(NormalExcelConstants.DATA_LIST,data);
        }else{
            map.put(NormalExcelConstants.CLASS,FoodDemoEntity.class);
            map.put(NormalExcelConstants.PARAMS,new ExportParams("淘宝/天猫清单", "销售时间xxx", "无糖食品"));
            map.put(NormalExcelConstants.DATA_LIST,data);
        }
        return map;
    }


    public static Workbook exportExcel(List<Map<String ,Object>> list , ExcelType type){
        Workbook workbook;
        if(ExcelType.HSSF.equals(type)){
            workbook = new HSSFWorkbook();
        }else{
            workbook = new XSSFWorkbook();
        }
        for (Map<String,Object> map :list){
            ExcelExportServer server = new ExcelExportServer();
//            ExcelController server = new ExcelExportServer();
            ExportParams param = (ExportParams)map.get("params");
            Class<?> entry = (Class<?>)map.get("entity");
            Collection<?> data = (Collection<?>)map.get("data");
            server.createSheet(workbook,param,entry,data);
        }
        return workbook;
    }


    /**
     * 导出逻辑
     */
    @RequestMapping(value = "/exportXls.do")
    public void exportExcelFile(HttpServletRequest request, HttpServletResponse response, @RequestParam("fileName")String fileName, ModelMap modelMap) {

        Map<String,Object> map = new HashMap<>();
        map.put("sheetnum", "1");
        List<ExcelDemoEntity> list = excelService.queryList(map);
        List<TaobaoDemoEntity> taobaoList = new ArrayList<TaobaoDemoEntity>();
        List<JingDongDemoEntity> jingdongList = new ArrayList<JingDongDemoEntity>();
        List<TianMaoDemoEntity> tianmaoList = new ArrayList<TianMaoDemoEntity>();
        String sheetName = "天猫";

        for(int i=0;i<list.size();i++){
            if(list.get(i).getSheet().equals("京东新店")){
                JingDongDemoEntity jingdong = new JingDongDemoEntity();
                jingdong.setOrdercode(list.get(i).getOrdercode());
                jingdong.setName(list.get(i).getName());
                jingdong.setG56(list.get(i).getG56());
                jingdong.setG156(list.get(i).getG156());
                jingdong.setG180(list.get(i).getG180());
                jingdong.setG380(list.get(i).getG380());
                jingdong.setU500(list.get(i).getU500());
                jingdong.setG550(list.get(i).getG550());
//                jingdong.setG6(list.get(i).getG6());
                jingdong.setMoney(list.get(i).getMoney());
                jingdong.setWeight(list.get(i).getWeight());
                jingdong.setExpress(list.get(i).getExpress());
                jingdong.setPostage(list.get(i).getPostage());
                jingdong.setRemarks(list.get(i).getRemarks());
                jingdongList.add(jingdong);
                sheetName = list.get(i).getSheet();

            }else if(list.get(i).getSheet().equals("天猫")){
                TianMaoDemoEntity tianmao = new TianMaoDemoEntity();
                tianmao.setOrdercode(list.get(i).getOrdercode());
                tianmao.setName(list.get(i).getName());
                tianmao.setG56(list.get(i).getG56());
                tianmao.setG156(list.get(i).getG156());
                tianmao.setG180(list.get(i).getG180());
                tianmao.setG380(list.get(i).getG380());
                tianmao.setG550(list.get(i).getG550());
                tianmao.setU500(list.get(i).getU500());
                tianmao.setMoney(list.get(i).getMoney());
                tianmao.setWeight(list.get(i).getWeight());
                tianmao.setExpress(list.get(i).getExpress());
                tianmao.setPostage(list.get(i).getPostage());
                tianmao.setRemarks(list.get(i).getRemarks());
                tianmaoList.add(tianmao);
                sheetName = list.get(i).getSheet();
            }else if(list.get(i).getSheet().equals("万春")){
                JingDongDemoEntity wanchun = new JingDongDemoEntity();
                wanchun.setOrdercode(list.get(i).getOrdercode());
                wanchun.setName(list.get(i).getName());
                wanchun.setG56(list.get(i).getG56());
                wanchun.setG156(list.get(i).getG156());
                wanchun.setG180(list.get(i).getG180());
                wanchun.setG380(list.get(i).getG380());
                wanchun.setU500(list.get(i).getU500());
                wanchun.setG550(list.get(i).getG550());
                wanchun.setMoney(list.get(i).getMoney());
                wanchun.setWeight(list.get(i).getWeight());
                wanchun.setExpress(list.get(i).getExpress());
                wanchun.setPostage(list.get(i).getPostage());
                wanchun.setRemarks(list.get(i).getRemarks());
                jingdongList.add(wanchun);
                sheetName = list.get(i).getSheet();
            }else{
                TaobaoDemoEntity taobao = new TaobaoDemoEntity();
                taobao.setOrdercode(list.get(i).getOrdercode());
                taobao.setName(list.get(i).getName());
                taobao.setG56(list.get(i).getG56());
                taobao.setG156(list.get(i).getG156());
                taobao.setG180(list.get(i).getG180());
                taobao.setG200(list.get(i).getG200());
                taobao.setG300(list.get(i).getG300());
                taobao.setG380(list.get(i).getG380());
                taobao.setG500(list.get(i).getG500());
                taobao.setU500(list.get(i).getU500());
                taobao.setG550(list.get(i).getG550());
                taobao.setG6(list.get(i).getG6());
                taobao.setMoney(list.get(i).getMoney());
                taobao.setWeight(list.get(i).getWeight());
                taobao.setExpress(list.get(i).getExpress());
                taobao.setPostage(list.get(i).getPostage());
                taobao.setRemarks(list.get(i).getRemarks());
                taobaoList.add(taobao);
                sheetName = list.get(i).getSheet();
            }


        }
        Map<String,Object> map11;
        if(sheetName.equals("天猫")){
            map11 = getTestMap("1",tianmaoList);
        }else if(sheetName.equals("京东新店")){
            map11 = getTestMap("5",jingdongList);
        }else if(sheetName.equals("万春")){
            map11 = getTestMap("5",jingdongList);
        }else{
            map11 = getTestMap("4",taobaoList);
        }

        Map<String,Object> map2 = new HashMap<>();
        map.put("sheetnum", "2");
        List<ExcelDemoEntity> list2 = excelService.queryList(map);
        List<FruitDemoEntity> fruitList = new ArrayList<FruitDemoEntity>();
        for(int i=0;i<list2.size();i++){
            FruitDemoEntity fruit = new FruitDemoEntity();
            fruit.setOrdercode(list2.get(i).getOrdercode());
            fruit.setName(list2.get(i).getName());
            fruit.setKg2(list2.get(i).getKg2());
            fruit.setKg25(list2.get(i).getKg25());
            fruit.setKgy25(list2.get(i).getKgy25());
            fruit.setMoney(list2.get(i).getMoney());
            fruit.setExpress(list2.get(i).getExpress());
            fruit.setPostage(list2.get(i).getPostage());
            fruit.setRemarks(list2.get(i).getRemarks());
            fruitList.add(fruit);
        }
        map.put("sheetnum", "3");
        List<ExcelDemoEntity> list3 = excelService.queryList(map);
        List<FoodDemoEntity> foodList = new ArrayList<FoodDemoEntity>();
        for(int i=0;i<list3.size();i++){
            FoodDemoEntity food = new FoodDemoEntity();
            food.setOrdercode(list3.get(i).getOrdercode());
            food.setName(list3.get(i).getName());
            food.setRedbean(list3.get(i).getRedbean());
            food.setLongan(list3.get(i).getLongan());
            food.setWax(list3.get(i).getWax());
            food.setDcz(list3.get(i).getDcz());
            food.setHtf(list3.get(i).getHtf());
            food.setS500(list3.get(i).getS500());
            food.setMoney(list3.get(i).getMoney());
            food.setExpress(list3.get(i).getExpress());
            food.setPostage(list3.get(i).getPostage());
            food.setRemarks(list3.get(i).getRemarks());
            foodList.add(food);
        }
//        Map<String,Object> map11 = getTestMap("1",list);
        Map<String,Object> map22 = getTestMap("2",fruitList);
        Map<String,Object> map33 = getTestMap("3",foodList);
        List<Map<String ,Object>> listall = new ArrayList<>();
        listall.add(map11);
        listall.add(map22);
        listall.add(map33);
        Workbook workbook = exportExcel(listall , ExcelType.HSSF);
        try{
            request.setCharacterEncoding("UTF-8");
            response.setCharacterEncoding("UTF-8");
//            response.setCharacterEncoding("application/x-download");
            String filedisplay = "华康清单.xls";
            filedisplay = URLEncoder.encode(filedisplay,"UTF-8");
            response.addHeader("Content-Disposition","attachment;filename="+filedisplay);
            OutputStream out = response.getOutputStream();
            workbook.write(out);
            out.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    /**
     * 导入excel文件
     * @param
     */
    @RequestMapping(value = "/importExcel.json")
    @ResponseBody
    public Map<String, Object> importExcelFile(HttpServletRequest request, @RequestParam("fileupload")MultipartFile file) {
        Map<String, Object> resultMap = new HashMap<>();
        String fileName = file.getOriginalFilename();
        // 文件后缀
        String suffix = getSuffix(fileName);
        if (!excelTypeList.contains(suffix)) {
            resultMap.put("success", false);
            resultMap.put("message", "error");
            return resultMap;
        }
        // check文件是否存在,开发用，先注释。
//        boolean isExiste = execlFileService.checkFileIsExiste(fileName);
//        if (isExiste) {
//            resultMap.put("success", false);
//            resultMap.put("message", "file is already existe");
//            return resultMap;
//        }
        // 封装实体对象
        ExeclFileEntity execlFileEntity = new ExeclFileEntity();
        execlFileEntity.setCreateDate(new Date());
        execlFileEntity.setEditDate(new Date());
        execlFileEntity.setExecelName(fileName);
        execlFileService.saveExeclFile(execlFileEntity);
        // 查询一个实体对象
        final ExeclFileEntity newExeclFileEntity = execlFileService.queryExeclFileEntity(fileName);
        // 保存文件名到excel_file表
        // 导入excel
        ImportParams importParams = new ImportParams();
        // 设置导入的头为1
//        importParams.setHeadRows(1);
        //设置不为空的主键.
       importParams.setKeyIndex(23);
        try {
            List<ExcelDemoEntity> list = ExcelImportUtil.importExcel(file.getInputStream(), ExcelDemoEntity.class,importParams);
            // 保存数据库 ,,空数据怎么不进来哦。
          excelService.saveList(list,newExeclFileEntity,fileName);

//中铁
//            List<CrlgDemoEntity> list = ExcelImportUtil.importExcel(file.getInputStream(), CrlgDemoEntity.class,importParams);
//            crlgService.saveList(list,newExeclFileEntity,fileName);
        } catch (Exception e) {
            e.printStackTrace();
            resultMap.put("success", false);
            resultMap.put("message", "error");
            return resultMap;
        }
        resultMap.put("success", true);
        resultMap.put("message", "success");
        return resultMap;
    }

    /**
     * 获取文件的后缀
     * @param fileName
     * @return
     */
    private String getSuffix(String fileName) {
        try {
            return fileName.substring(fileName.lastIndexOf(".", fileName.length() - 1), fileName.length());
        } catch (Exception e) {
            return StringUtils.EMPTY;
        }
    }

    @RequestMapping("/list")
    @ResponseBody
    public R list(String beanName, Integer page, Integer limit){
        Map<String, Object> map = new HashMap<>();
        map.put("excelId", beanName);
        map.put("offset", (page - 1) * limit);
        map.put("limit", limit);

        List<ExcelDemoEntity> excelList = excelService.queryList(map);
        int total = excelService.queryTotal(map);

        PageUtils pageUtil = new PageUtils(excelList, total, limit, page);
        return R.ok().put("page", pageUtil);
    }

    @RequestMapping("/output")
    @ResponseBody
    public void output() throws Exception {
        Map<String, Object> value = new HashMap<String, Object>();
        TemplateExportParams params = new TemplateExportParams("io/renren/controller/excel/for_Col.xlsx", true);
        System.out.println(this.getClass().getResource("").getPath());
        params.setColForEach(true);
        Workbook book = ExcelExportUtil.exportExcel(params, value);
        PoiMergeCellUtil.mergeCells(book.getSheetAt(0), 1, 0,1);
        FileOutputStream fos = new FileOutputStream("D:/excel/ExcelExportTemplateColFeTest_one.xlsx");
        book.write(fos);
        fos.close();

    }
    /**
     * 删除配置
     */
    @RequestMapping("/dele")
    @ResponseBody
    public R dele(){
        excelService.deleteAll();
        return R.ok();
    }


}
